"""
Case Type   : 事务-ustore
Case Name   : 事务->Drop Table->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建表
    2.开启事务清理环境再回滚
    3.kill数据库
    4.查询数据
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.无法查询
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table1 = 't_ustore_trans_0003_01'
        self.table2 = 't_ustore_trans_0003_02'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop table if exists {self.schema}.{self.table1} cascade;
            drop table if exists {self.schema}.{self.table2} cascade;
            create table {self.schema}.{self.table1}(c_id int,c_d_id int not null,
            c_w_id int not null,c_first varchar(16) not null,c_middle 
            char(22),c_last varchar(16) not null,c_street_1 varchar(20) 
            not null,c_street_2 varchar(20),c_city varchar(20) not null,
            c_state char(22) not null,c_zip char(9) not null,c_phone char(16) 
            not null,c_since timestamp,c_credit char(22) not null,
            c_credit_lim numeric(22,12),c_discount numeric(4,4),c_balance 
            numeric(22,12),c_ytd_payment real not null,c_payment_cnt number 
            not null,c_delivery_cnt bool not null,c_end date not null,c_data 
            clob,c_clob clob) with (storage_type=ustore);
            insert into {self.schema}.{self.table1} 
            select * from {self.schema}.t_ustore_trans_0001;
            create unique index {self.schema}.index_trans_0003_01 on 
            {self.schema}.{self.table1}(c_id);
            create index {self.schema}.index_trans_0003_02 on 
            {self.schema}.{self.table1}(c_first);
            create index {self.schema}.index_trans_0003_03 on 
            {self.schema}.{self.table1}(c_first,c_middle,c_last);
            create index {self.schema}.index_trans_0003_04 on 
            {self.schema}.{self.table1}(c_first,c_id);
            create table {self.schema}.{self.table2}(c_id int,c_d_id int not null,c_w_id int not null,
            c_first varchar(16) not null,c_middle char(22),c_last varchar(16) not null,c_street_1 varchar(20)
            not null,c_street_2 varchar(20),c_city varchar(20) not null,c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,c_since timestamp,c_credit char(22) not null,c_credit_lim
            numeric(22,12),c_discount numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,
            c_payment_cnt number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,
            c_clob clob) with (storage_type=ustore);
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, q_res,
              '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '----step2开启事务清理环境再回滚;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
            drop table {self.schema}.{self.table1};
            drop table {self.schema}.{self.table2};
            rollback;'''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)

        text = '----step3:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:(2 rows)----'
        self.log.info(text)
        q_str = f"select relname from pg_class " \
                f"where relname like 't_ustore_trans_0003%' " \
                f"order by relname desc;"
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertIn('2 rows', q_res, '执行失败: ' + text)
        self.assertIn(self.table1, q_res, '执行失败: ' + text)
        self.assertIn(self.table2, q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.schema}.{self.table1},' \
              f'{self.schema}.{self.table2} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
